package com.led.datas;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.led.datatype.DataBasic;
import com.led.datatype.DataReal;
import com.led.datatype.LedData;
import com.led.db.DBMgr;

public class DataAccess {
	static final String SQL_QUERY_DATAS_ALL = 
			"select b.id, b.info, d.datas from datas_basic b, datas_real d "
		   + "where b.idx_datas = d.id";
	
	static final String SQL_QUERY_DATAS_BASIC = 
			"select id, idx_datas, info from datas_basic";

	static final String SQL_QUERY_DATAS_REAL_BY_IDX = 
			"select datas from datas_real where id = ?";
	
	static final String SQL_INSERT_DATAS_BASIC = 
			"insert into datas_basic(idx_datas, info) values(?,?)";
	
	static final String SQL_INSERT_DATAS_REAL = 
			"insert into datas_real(datas) values(?)";
	
//	public static 
	public static void main(String[] args) {
		System.out.println( getAllDatas());
		System.out.println( getDatasBasic());
		System.out.println( getDatasReal(1));
//		System.out.println(saveToDB("{\"info\":\"test 2\",\"datas\":\"2 2 2 2\"}"));
	}

	public static String getDatasBasic(){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when getDatasBasic, con is null!");
		}

		JSONArray js = new JSONArray();
		ResultSet rs =null;
		try {
			Statement ps = con.createStatement();
			rs = ps.executeQuery(SQL_QUERY_DATAS_BASIC);
			if(null == rs){
				System.out.println("no datas exists in getDatasBasic");
				return "";
			}

			LinkedList<DataBasic> dataList = DataBasic.getDatas(rs);
			for(DataBasic ld: dataList){
				js.add(ld.toJsonStr());
			}
		} catch (SQLException e) {
			e.printStackTrace();
			DBMgr.reset();
		} finally{
			if(null != rs){
				try{rs.close();} catch(Exception e){}
			}

		}
		return js.toString();
	}

	public static String getDatasReal(int idxRealDatas){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when getDatasReal, con is null!");
		}

		JSONArray js = new JSONArray();
		ResultSet rs =null;
		try {
			PreparedStatement ps = con.prepareStatement(SQL_QUERY_DATAS_REAL_BY_IDX);
			ps.setInt(1, idxRealDatas);;
			rs = ps.executeQuery();
			if(null == rs){
				System.out.println("no datas exists in getDatasReal, idx:" + idxRealDatas);
				return "";
			}

			LinkedList<DataReal> dataList = DataReal.getDatas(rs);
			for(DataReal ld: dataList){
				js.add(ld.toJsonStr());
			}
		} catch (SQLException e) {
			e.printStackTrace();
			DBMgr.reset();
		} finally{
			if(null != rs){
				try{rs.close();} catch(Exception e){}
			}

		}
		return js.toString();
	}
	public static String getAllDatas(){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when getAllDatas, con is null!");
		}

		JSONArray js = new JSONArray();
		ResultSet rs =null;
		try {
			Statement ps = con.createStatement();
			rs = ps.executeQuery(SQL_QUERY_DATAS_ALL);
			if(null == rs){
				System.out.println("no datas exists");
				return "";
			}

			LinkedList<LedData> dataList = LedData.getDatas(rs);
			for(LedData ld: dataList){
				js.add(ld.toJsonStr());
			}
		} catch (SQLException e) {
			e.printStackTrace();
			DBMgr.reset();
		} finally{
			if(null != rs){
				try{rs.close();} catch(Exception e){}
			}

		}
		return js.toString();
	}

	public static ResultSet getAllDatasResultSet(){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when getAllDatas, con is null!");
			return null;
		}
		try {
			Statement ps = con.createStatement();
			ResultSet rs = ps.executeQuery(SQL_QUERY_DATAS_ALL);
			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
			DBMgr.reset();
		}
		return null;
	}
	public static boolean saveToDB(String jsLedData){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when in SaveToDB, connect is null");
			return false;
		}

		//maybe insert datas is invalid!
		LedData ledData  = null;
		try{
			String jsDatas = jsLedData.replaceAll("\t|\r|\n", " "); //
			jsDatas = jsDatas.replaceAll("[ ]+", " ");
			ledData  = new LedData(JSONObject.fromObject(jsDatas));
		} catch (Exception e){
			e.printStackTrace();
			return false;
		}
		PreparedStatement ps = null;
		 try {
			 con.setAutoCommit(false);
//			 final String insertSql = "insert into leddatas(info, datas) values(?, ?)";
			 ps = con.prepareStatement(SQL_INSERT_DATAS_REAL);
			 ps.setString(1, ledData.getDatas());
			 int result = ps.executeUpdate();
			 if(result != 1){
				 System.out.println("insert " + ledData + " success, tab:datas_real"
				 		+ ", pos:" + result);
				 con.rollback();
				 return false;
			 }

			 int pos = getLastInsertNum(con);
			 if(pos <= 0){
				System.out.println("call getLastInsertNum after insert to datas_real failed! pos:" + pos);
				con.rollback();
				return false;
			 } else {
				System.out.println("call getLastInsertNum after insert to datas_real success! pos:" + pos);
			 }

			 ps = con.prepareStatement(SQL_INSERT_DATAS_BASIC);
			 ps.setInt(1, pos);
			 ps.setString(2, ledData.getInfo());
			 result =  ps.executeUpdate();
			 if(result == 1){
				 System.out.println("insert " + ledData + " success, result:" + result);
				 con.commit();
			 } else {
				 System.out.println("insert " + ledData + " failed!, result:" + result);
				 con.rollback();
				 return false;
			 }

		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

			DBMgr.reset();
			e.printStackTrace();
		} finally {
			try {
				if(null != con){
				   con.setAutoCommit(true);
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

			if(null != ps){
				try{
					ps.close();
				}catch(Exception e){
				}
			}

		}
		return true;
	}

	private static int getLastInsertNum(Connection con ){
		 PreparedStatement ps;
		try {
			ps = con.prepareStatement("select LAST_INSERT_ID()");
			ResultSet rs = ps.executeQuery();
			if(null == rs || rs.isAfterLast()){
				System.out.println("call getLastInsertNum failed! rs is null or after last");
				return -1;
			} else {
				rs.next();
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
}
